package com.xinan.xs;

import com.jfinal.aop.Before;
import com.jfinal.core.BaseController;
import com.jfinal.core.Const;
import com.jfinal.ext.interceptor.SearchSql;
import com.jfinal.kit.JsonKit;
import com.jfinal.kit.StringKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.xinan.dmgl.cggl.CgglWp;
import com.xinan.dmgl.cggl.CpglCk;
import com.xinan.dmgl.cggl.CpglRk;
import com.xinan.hygl.Hygl;
import static com.xinan.utils.CommonUtil.toUtf8String;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: xiaojing
 * Date: 14-12-10
 * Time: 上午11:54
 * To change this template use File | Settings | File Templates.
 */
public class TjController  extends BaseController{
    //会员统计
    public  void hytj(){
        render("/WEB-INF/views/tj/hyktj.html");
    }
  // 会员赠送产品统计
    public void hyzscptj(){
        render("/WEB-INF/views/tj/hyzstj.html");
    }
  
    //会员充值记录
    public  void hycc(){
        render("/WEB-INF/views/tj/hycztj.html");
    }
    
    //会员开卡记录
    public void hykc(){
    	render("/WEB-INF/views/tj/hykktj.html");
    }
    //销售提成统计	
    public  void xstctj(){
        render("/WEB-INF/views/tj/xstctj.html");
    }
    
    @Before(SearchSql.class)
    public void querhycc() {
    	int pageNumber = getParaToInt("page");
		int pageSize = getParaToInt("rows");
        String where = getAttr(Const.SEARCH_SQL);
		Page<HyglCz> page = HyglCz.dao.pagetj(pageNumber, pageSize, where);
		Map root = new HashMap();
		// / jqgrid用 分页显示
		root.put("total", page.getTotalPage());
		root.put("page", page.getPageNumber());
		root.put("records", page.getTotalRow());
		root.put("rows", page.getList());
		renderJson(JsonKit.toJson(root));
    }
    /* 统计开卡记录 */
    @Before(SearchSql.class)
    public void querhykc() {
    	int pageNumber = getParaToInt("page");
		int pageSize = getParaToInt("rows");
        String where = getAttr(Const.SEARCH_SQL);
		Page<Hygl> page = Hygl.dao.pagekktj(pageNumber, pageSize, where);
		Map root = new HashMap();
		// / jqgrid用 分页显示
		root.put("total", page.getTotalPage());
		root.put("page", page.getPageNumber());
		root.put("records", page.getTotalRow());
		root.put("rows", page.getList());
		renderJson(JsonKit.toJson(root));
    }
    
    //商品出
    public  void spck(){
        render("/WEB-INF/views/tj/spck.html");
    }
    //商品进销存统计
    public  void spjxctj(){
        render("/WEB-INF/views/tj/jxctj.html");
    }
    /**
     * 在增加了SearchSql拦截之后就可以根据页面配置信息
     * 自动装配查询语句放入request中,通过getAttr(Const.SEARCH_SQL)函数获得 根据实际需要自动组装到Sql语句里面
     */
    @Before(SearchSql.class)
    public void queryjxctj() {
    	int pageNumber = getParaToInt("page");
		int pageSize = getParaToInt("rows");
        String where = getAttr(Const.SEARCH_SQL);
		Page<CgglWp> page = CgglWp.dao.pagetj(pageNumber, pageSize, where);
		Map root = new HashMap();
		// / jqgrid用 分页显示
		root.put("total", page.getTotalPage());
		root.put("page", page.getPageNumber());
		root.put("records", page.getTotalRow());
		root.put("rows", page.getList());
		renderJson(JsonKit.toJson(root));
    }
    
    
    /**
     * 在增加了SearchSql拦截之后就可以根据页面配置信息
     * 自动装配查询语句放入request中,通过getAttr(Const.SEARCH_SQL)函数获得 根据实际需要自动组装到Sql语句里面
     */
    @Before(SearchSql.class)
    public void queryhytj() {
        int pageNumber = getParaToInt("page");
        int pageSize = getParaToInt("rows");
        String where = getAttr(Const.SEARCH_SQL);
        Page<Hygl> page = Hygl.dao.pagetj(pageNumber, pageSize, where);
        Map root = new HashMap();
        // / jqgrid用 分页显示
        root.put("total", page.getTotalPage());
		root.put("page", page.getPageNumber());
		root.put("records", page.getTotalRow());
		root.put("rows", page.getList());
		renderJson(JsonKit.toJson(root));
    }
  
    //会员统计合计
    @Before(SearchSql.class)
    public void queryhytjhj() {
        String sqlExp="select count(*) as sums " +
                ",ifnull( CAST(ysje   as  DECIMAL(8,1)) ,0) as  ysjes," +
                "ifnull( (select   sum(CAST(czje   as  DECIMAL(8,1)))  from  hygl_cz  ) ,0) as czje, " +
                "ifnull( (select   sum(CAST(vipcard   as  DECIMAL(8,1)))  from  hygl_jz   ) ,0) as vipcard,  " +
                " ifnull( (select   sum(CAST(moneys   as  DECIMAL(8,1)))  from  hygl_jz  a ) ,0) as moneys," +
                "ifnull( (select   sum(CAST(xfjf   as  DECIMAL(8,1)))  from  jfjl_hy  a where a.card=s.card  ),0)  as xfjfs  from hygl s" ;
        String where = getAttr(Const.SEARCH_SQL);
        if(StringKit.notBlank(where)){
            sqlExp += " where " + where;
        }
        List<Record> newlist = Db.find(sqlExp);

        renderJson(newlist);
    }
    
  //充值记录合计
    @Before(SearchSql.class)
    public void queryczjlhj() {
        String sqlExp="select count(*) as sums,sum(CAST(czje AS DECIMAL(10,2))) as czje,"
        		+ "ifnull( (select   sum(CAST(vipcard   as  DECIMAL(8,1)))  from  hygl_jz   ) ,0) as vipcard"
        		+ " from hygl_cz";
        String where = getAttr(Const.SEARCH_SQL);
        if(StringKit.notBlank(where)){
            sqlExp += " where " + where;
        }
        List<Record> newlist = Db.find(sqlExp);

        renderJson(newlist);
    }
    
  //开卡记录合计
    @Before(SearchSql.class)
    public void querykkjlhj() {
        String sqlExp="select count(*) as sums,CAST(sum(ysje) AS DECIMAL(10,2)) as czje,"
        		+ "ifnull( (select   sum(CAST(vipcard   as  DECIMAL(8,1)))  from  hygl_jz   ) ,0) as vipcard"
        		+ " from hygl";
        String where = getAttr(Const.SEARCH_SQL);
        if(StringKit.notBlank(where)){
            sqlExp += " where " + where;
        }
        List<Record> newlist = Db.find(sqlExp);

        renderJson(newlist);
    }
    
    // 商品入库统计
    @Before(SearchSql.class)
    public void querspcktj() {
    	int pageNumber = getParaToInt("page");
        int pageSize = getParaToInt("rows");
        
        String where = getAttr(Const.SEARCH_SQL);
        Page<CpglCk> page = CpglCk.dao.pagetj(pageNumber, pageSize, where);
        Map root = new HashMap();
		// / jqgrid用 分页显示
        System.out.println("==========="+page.getTotalRow());
		root.put("total", page.getTotalPage());
		root.put("page", page.getPageNumber());
		root.put("records", page.getTotalRow());
		root.put("rows", page.getList());
		renderJson(JsonKit.toJson(root));
    }
    //客户消费
    public  void khxftj(){
        render("/WEB-INF/views/tj/khxftj.html");
    }
    /**
     * 在增加了SearchSql拦截之后就可以根据页面配置信息
     * 自动装配查询语句放入request中,通过getAttr(Const.SEARCH_SQL)函数获得 根据实际需要自动组装到Sql语句里面
     */
    @Before(SearchSql.class)
    public void querykhxf() {
        int pageNumber = getParaToInt("page");
        int pageSize = getParaToInt("rows");
        String where = getAttr(Const.SEARCH_SQL);
        Page<HyglXf> page = HyglXf.dao.page(pageNumber, pageSize, where);
        Map root = new HashMap();
        // / jqgrid用 分页显示
        root.put("total", page.getTotalPage());
		root.put("page", page.getPageNumber());
		root.put("records", page.getTotalRow());
		root.put("rows", page.getList());
		renderJson(JsonKit.toJson(root));
    }

    @Before(SearchSql.class)
    public void querykhxfhj() {
        String sqlExp="select count(*) as sums , sum(xfsl ) as xfsls ,sum(CAST(xftc as DECIMAL(10,2))) as xftc ," +
                "sum(CAST(xfsjjg as DECIMAL(10,2))) as xfsjjg  from hygl_xf" ;
        String where = getAttr(Const.SEARCH_SQL);
        if(StringKit.notBlank(where)){
            sqlExp += " where " + where;
        }
        List<Record> newlist = Db.find(sqlExp);
        renderJson(newlist);
    }
    //销售统计
    public  void xstj(){
        render("/WEB-INF/views/tj/xstj.html");
    }
    /**
     * 在增加了SearchSql拦截之后就可以根据页面配置信息
     * 自动装配查询语句放入request中,通过getAttr(Const.SEARCH_SQL)函数获得 根据实际需要自动组装到Sql语句里面
     */
    @Before(SearchSql.class)
    public void queryxstj() {
        int pageNumber = getParaToInt("page");
        int pageSize = getParaToInt("rows");
        String where = getAttr(Const.SEARCH_SQL);
        Page<HyglXf> page = HyglXf.dao.pagexstj(pageNumber, pageSize, where);
        Map root = new HashMap();
        // / jqgrid用 分页显示
        root.put("total", page.getTotalPage());
		root.put("page", page.getPageNumber());
		root.put("records", page.getTotalRow());
		root.put("rows", page.getList());
		renderJson(JsonKit.toJson(root));
    }
    
    
    /**
     * 销售提成统计
     * 在增加了SearchSql拦截之后就可以根据页面配置信息
     * 自动装配查询语句放入request中,通过getAttr(Const.SEARCH_SQL)函数获得 根据实际需要自动组装到Sql语句里面
     */
    @Before(SearchSql.class)
    public void queryxstctj() {
        int pageNumber = getParaToInt("page");
        int pageSize = getParaToInt("rows");
        String where = getAttr(Const.SEARCH_SQL);
        Page<HyglXf> page = HyglXf.dao.pagexstctj(pageNumber, pageSize, where);
        Map root = new HashMap();
        // / jqgrid用 分页显示
        root.put("total", page.getTotalPage());
		root.put("page", page.getPageNumber());
		root.put("records", page.getTotalRow());
		root.put("rows", page.getList());
		renderJson(JsonKit.toJson(root));
    }
    //入库统计
    public  void rktj(){
        render("/WEB-INF/views/tj/rktj.html");
    }
    
    /**
	 * 在增加了SearchSql拦截之后就可以根据页面配置信息
	 * 自动装配查询语句放入request中,通过getAttr(Const.SEARCH_SQL)函数获得 根据实际需要自动组装到Sql语句里面
	 */
	@Before(SearchSql.class)
	public void rktjquery() {
		int pageNumber = getParaToInt("page");
		int pageSize = getParaToInt("rows");
		String where = getAttr(Const.SEARCH_SQL);
		Page<CpglRk> page = CpglRk.dao.page(pageNumber, pageSize, where);
		Map root = new HashMap();
		// / jqgrid用 分页显示
		root.put("total", page.getTotalPage());
		root.put("page", page.getPageNumber());
		root.put("records", page.getTotalRow());
		root.put("rows", page.getList());
		renderJson(JsonKit.toJson(root));
	}

    /**
     * 导出 基础数据excel
     */
    @Before(SearchSql.class)
    public  void hyktoexcel()   {
        String where = getAttr(Const.SEARCH_SQL);
        List<Record> list =Hygl.dao.findAlls(where)  ;
        String[] columns = new String[] { "card","yhm", "ysje", "czje","ye","vipcard","moneys","cph" ,"bxrq","njrq","bkrq" };
        String[] headers = new String[]  { "卡号","姓名", "原始金额", "充值金额" , "卡消费" ,"现金消费" ,"车款" ,"车牌号" ,"保险日期","年检日期","办卡日期"};
        render(com.xinan.utils.excel.PoiRender.me(list).fileName(toUtf8String("会员统计.xls")).sheetName("会员统计").columns(columns).headers(headers).cellWidth(3500).headerRow(1));

    }

    /**
     * 导出 基础数据excel
     */
    @Before(SearchSql.class)
    public  void khxftoexcel()   {
        String where = getAttr(Const.SEARCH_SQL);
        List<Record> list =HyglXf.dao.findAlls(where)  ;
        String[] columns = new String[] { "card","yhm", "xfmc", "xfsl","xfsjjg","lrrq" };
        String[] headers = new String[]  { "卡号","姓名", "消费名称", "消费数量" ,"消费金额" ,"日期" };
        render(com.xinan.utils.excel.PoiRender.me(list).fileName(toUtf8String("客户消费统计.xls")).sheetName("客户消费统计").columns(columns).headers(headers).cellWidth(3500).headerRow(1));

    }
    /**
     * 导出 基础数据excel
     */
    @Before(SearchSql.class)
    public  void xstjtoexcel()   {
        String where = getAttr(Const.SEARCH_SQL);
        List<Record> list =HyglXf.dao.findxstj(where)  ;
        String[] columns = new String[] {  "xfmc", "xfsl","xfsjjg","xftc","czymc","lrrq" };
        String[] headers = new String[]  {  "消费名称", "消费数量" ,"消费金额" ,"提成金额" ,"工人" ,"日期" };
        render(com.xinan.utils.excel.PoiRender.me(list).fileName(toUtf8String("销售统计.xls")).sheetName("销售统计").columns(columns).headers(headers).cellWidth(3500).headerRow(1));

    }
}
